#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
__title__ = ''
__author__ = 'ben'
__mtime__ = '16-4-14'
"""

import MySQLdb
import sys
import logging

logger = logging.getLogger('WyDBHandler')
logger.setLevel(logging.DEBUG)
fh = logging.FileHandler('/home/log/WyDBFactory.log')
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
fh.setFormatter(formatter)
logger.addHandler(fh)


class WyDBHandler(object):
    conn = None

    def __init__(self, host, user, passwd, db, port=3306):
        if not self.conn:
            try:
                self.conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, port=port)
                logger.log(logging.INFO, __file__ + '|' + str(sys._getframe().f_lineno) + '|' + 'connect db-%s(%s:%d@%s/%s) succeed'%(db,host,port,user,passwd))
            except Exception,e:
                logger.log(logging.INFO, __file__ + '|' + str(sys._getframe().f_lineno) + '|' + str(e))
                self.conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, port=port)

    def re_connect(self, host, user, passwd, db, port=3306):
        if not self.conn:
            try:
                self.conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, port=port)
                logger.log(logging.INFO, __file__ + '|' + str(sys._getframe().f_lineno) + '|' + 'connect db-%s(%s:%d@%s/%s) succeed' % (db, host, port, user, passwd))
            except Exception, e:
                logger.log(logging.INFO, __file__ + '|' + str(sys._getframe().f_lineno) + '|' + str(e))
                self.conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, port=port)
        else:
            try:
                cur = self.conn.cursor()
                if cur:
                    cur.close()

                # ping
                ping_res = self.conn.ping()
                if ping_res:
                    raise Exception(__file__ + '|' + str(sys._getframe().f_lineno) + '|' + 'ping failed')

            except Exception, e:
                logger.log(logging.DEBUG, __file__ + '|' + str(sys._getframe().f_lineno) + '|' + '__init__/' + str(e))
                self.conn.close()
                self.conn = None
                self.conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, port=port)

    def execute(self, sql):
        cur = None
        row_count = 0
        try:
            cur = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
            set_chartset = "set names 'utf8'"
            cur.execute(set_chartset)

            row_count = cur.execute(sql)
        except Exception,e:
            logging.log(logging.INFO,__file__ + '|' + str(sys._getframe().f_lineno) + '|' + 'execute/'+str(e))

        cur.close()
        return row_count

    def get_one(self, sql):
        result = {}
        cur = None

        try:
            cur = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
            set_chartset = "set names 'utf8'"
            cur.execute(set_chartset)

            row_count = cur.execute(sql)
            if row_count > 0:
                result = cur.fetchone()

        except Exception, e:
            logging.log(logging.INFO, __file__ + '|' + str(sys._getframe().f_lineno) + '|' + 'get_one/'+str(e))

        cur.close()
        return result

    def get_all(self, sql):
        result = []
        cur = None

        try:
            cur = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
            set_chartset = "set names 'utf8'"
            cur.execute(set_chartset)

            row_count = cur.execute(sql)
            if row_count > 0:
                result = cur.fetchall()
        except Exception, e:
            logging.log(logging.INFO, 'get_all/'+str(e))

        cur.close()
        return result

    def get_many(self, sql, count):
        result = []
        cur = None

        try:
            cur = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
            set_chartset = "set names 'utf8'"
            cur.execute(set_chartset)

            row_count = cur.execute(sql)
            if row_count > 0:
                db_result = cur.fetchmany(count)

                if db_result:
                    for cmp in db_result:
                        result.append(str(cmp[0]))
        except Exception, e:
            logging.log(logging.INFO, 'get_many/'+str(e))

        cur.close()
        return result
